﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Business;
using Model;
using System.Web.Security;
using MB.Util;
using System.Data.OleDb;
using System.Data;
using System.Transactions;
using System.IO;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace MvcAppWeb.Controllers
{
    public class LocationInventoryController : Controller
    {
        //
        // GET: /LocationInventory/

        public ActionResult LocationInventory()
        {
            if (Session["UserInfo"] == null) return RedirectToAction("LogOn", "Account");
            else
            {
                AppUserList model = (AppUserList)Session["UserInfo"];
                return View(model);
            }
        }

        public JsonResult Query(string GOODSBATCHCODE, string LOCATIONCODE, string GOODSCODE, string INVGOODSCODE, string GOODSNAME, string INVGOODSNAME)
        {
            int page = int.Parse(Request["page"].ToString());
            int rows = int.Parse(Request["rows"].ToString());
            int total = 0;
            string where = " where 1=1";
            if (!string.IsNullOrEmpty(GOODSBATCHCODE))
            {
                where += @" and vl.GOODSBATCHCODE like'%" + GOODSBATCHCODE + "%' or vl.INVGOODSBATCHCODE like'%" + GOODSBATCHCODE + "%'";
            }
            if (!string.IsNullOrEmpty(LOCATIONCODE))
            {
                where += " and ll.LOCATIONCODE like'%" + LOCATIONCODE + "%'";
            }
            if (!string.IsNullOrEmpty(GOODSCODE))
            {
                where += " and g.GOODSCODE like'%" + GOODSCODE + "%'";
            }
            if (!string.IsNullOrEmpty(INVGOODSCODE))
            {
                where += " and g1.GOODSCODE like'%" + INVGOODSCODE + "%'";
            }
            if (!string.IsNullOrEmpty(GOODSNAME))
            {
                where += " and g.GOODSNAME like'%" + GOODSNAME + "%'";
            }
            if (!string.IsNullOrEmpty(INVGOODSNAME))
            {
                where += " and g1.GOODSNAME like'%" + INVGOODSNAME + "%'";
            }
            BLocation nc = new BLocation();
            var result = nc.GetLocationInventoryList(page, rows, out total, where);

            Dictionary<string, object> json = new Dictionary<string, object>();
            json.Add("total", total);
            json.Add("rows", result);
            return Json(json, JsonRequestBehavior.AllowGet);
        }

        [HttpPost]
        public FileResult ExportExcel(int page, int rows)//导出execl
        {
            //创建Excel文件的对象
            HSSFWorkbook book = new HSSFWorkbook();
            //添加一个sheet
            ISheet sheet1 = book.CreateSheet("Sheet1");
            #region 获取list数据
            int total = 0;
            string GOODSBATCHCODE = Request["txtGOODSBATCHCODE"];
            string LOCATIONCODE = Request["txtLOCATIONCODE"];
            string GOODSCODE = Request["txtGOODSCODE"];
            string INVGOODSCODE = Request["txtINVGOODSCODE"];
            string GOODSNAME = Request["txtGOODSNAME"];
            string INVGOODSNAME = Request["txtINVGOODSNAME"];

            string where = " where 1=1";
            if (!string.IsNullOrEmpty(GOODSBATCHCODE))
            {
                where += " and li.GOODSBATCHCODE like'%" + GOODSBATCHCODE + "%'";
            }
            if (!string.IsNullOrEmpty(LOCATIONCODE))
            {
                where += " and ll.LOCATIONCODE like'%" + LOCATIONCODE + "%'";
            }
            if (!string.IsNullOrEmpty(GOODSCODE))
            {
                where += " and g.GOODSCODE like'%" + GOODSCODE + "%'";
            }
            if (!string.IsNullOrEmpty(INVGOODSCODE))
            {
                where += " and g1.GOODSCODE like'%" + INVGOODSCODE + "%'";
            }
            if (!string.IsNullOrEmpty(GOODSNAME))
            {
                where += " and g.GOODSNAME like'%" + GOODSNAME + "%'";
            }
            if (!string.IsNullOrEmpty(INVGOODSNAME))
            {
                where += " and g1.GOODSNAME like'%" + INVGOODSNAME + "%'";
            }
            BLocation nc = new BLocation();
            List<LocationInventoryInfo> list = nc.GetLocationInventoryList(page, int.MaxValue, out total, where);
            #endregion
            //给sheet1添加第一行的头部标题
            IRow row1 = sheet1.CreateRow(0);
            List<string> lstTitle = new List<string>{ "商品批次号", "货位编码", "商品编码", "商品名称"
                , "下架商品编码", "下架商品名称","单位"
                , "仓库", "库位", "生产日期", "入库日期", "保质天数"
                , "库存数" , "有效天数" , "库存状态" };
            for (int i = 0; i < lstTitle.Count; i++)
            {
                row1.CreateCell(i).SetCellValue(lstTitle[i]);
            }
            //将数据逐步写入sheet1各个行
            for (int i = 0; i < list.Count; i++)
            {
                IRow rowtemp = sheet1.CreateRow(i + 1);
                rowtemp.CreateCell(0).SetCellValue(list[i].GOODSBATCHCODE);
                rowtemp.CreateCell(1).SetCellValue(list[i].LOCATIONCODE);
                rowtemp.CreateCell(2).SetCellValue(list[i].GOODSCODE);
                rowtemp.CreateCell(3).SetCellValue(list[i].GOODSNAME);
                rowtemp.CreateCell(4).SetCellValue(list[i].INVGOODSCODE);
                rowtemp.CreateCell(5).SetCellValue(list[i].INVGOODSNAME);
                rowtemp.CreateCell(6).SetCellValue(list[i].INVUNITNAME);
                rowtemp.CreateCell(7).SetCellValue(list[i].WAREHOUSENAME);
                rowtemp.CreateCell(8).SetCellValue(list[i].CELLNO);
                rowtemp.CreateCell(9).SetCellValue(list[i].PRODUCTIONDATE);
                rowtemp.CreateCell(10).SetCellValue(list[i].STORAGEDATE);
                rowtemp.CreateCell(11).SetCellValue(list[i].EXPIRYDAYS);
                rowtemp.CreateCell(12).SetCellValue(list[i].QTY.ToString());
                rowtemp.CreateCell(13).SetCellValue(GetValidDay(list[i].PRODUCTIONDATE, list[i].EXPIRYDAYS));
                rowtemp.CreateCell(14).SetCellValue(list[i].INVSTATUS);
            }
            // 写入到客户端 
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            return File(ms, "application/vnd.ms-excel", NewTimeStamp("LocationInventory"));
        }

        public string NewTimeStamp(string strFileName)
        {
            //生成文件名: 当前年月日小时分钟秒+ 随机数
            Random rd = new Random(int.Parse(DateTime.Now.ToString("MMddhhmmss")));
            strFileName += DateTime.Now.ToString("yyyyMMdd")
                + DateTime.Now.Hour
                + DateTime.Now.Minute
                + DateTime.Now.Second
                + rd.Next(999999).ToString()
                + ".xls";
            return strFileName;
        }

        public string GetValidDay(string PRODUCTIONDATE, int EXPIRYDAYS)
        {
            try
            {
                var PRODUCTIONYEAR = int.Parse(PRODUCTIONDATE.Substring(0, 1));
                var PRODUCTIONMONTH = int.Parse(PRODUCTIONDATE.Substring(1, 2));
                var PRODUCTIONDAY = int.Parse(PRODUCTIONDATE.Substring(3, 2));
                var date = DateTime.Now;
                var year = int.Parse(date.Year.ToString().Substring(3, 1));
                var month = date.Month;
                var day = date.Day;
                var pastdate = (year - PRODUCTIONYEAR) * 365 + (month - PRODUCTIONMONTH) * 30 + (day - PRODUCTIONDAY);
                if (pastdate >= 0 && EXPIRYDAYS - pastdate > 0)
                {
                    return (EXPIRYDAYS - pastdate).ToString();
                }
                else
                {
                    return "0";
                }
            }
            catch
            {
                return "0";
            }
        }
    }
}
